/*
 * @copyright: Huang Ding
 * @Author: ding-cx
 * @Date: 2021-03-27 17:24:33
 * @LastEditors: ding-cx
 * @LastEditTime: 2021-03-31 12:02:45
 * @Description: 查询数据的mysql封装。类似php ci里的方法调用。一个接口，前端即可通用查询多表关联
 */

import { Service } from "egg";
// import * as mysql from "mysql2";
// import * as sequelize from "sequelize"

type whereCondition = "=" | "!=" | "<" | ">" | "<=" | ">=" | "like";

export default class QuerySqlService extends Service {
  private _select = "SELECT ";
  private _from = "FROM ";

  private selectArgs: string[] = [];

  private fromArgs: string[] = [];

  /** select字段 */
  select(args: string | string[] | [string, string][]) {
    if (args === "*") {
      this.selectArgs.push(args);
      return;
    }

    if (typeof args === "string") {
      if (args.indexOf(" AS ") > -1) {
        const argsAs = args.split(" AS ");
        // 传入数组时可使用AS别名。
        let escapeStr =
          this.fieldWarpQuot(argsAs[0]) +
          " AS " +
          this.fieldWarpQuot(argsAs[1]);
        this.selectArgs.push(escapeStr);
      } else {
        let escapeStr = this.fieldWarpQuot(args);
        this.selectArgs.push(escapeStr);
      }
    } else if (Array.isArray(args)) {
      args.forEach((args) => {
        if (typeof args === "string") {
          let escapeStr = this.fieldWarpQuot(args);
          this.selectArgs.push(escapeStr);
        } else if (Array.isArray(args) && (args as string[]).length === 2) {
          // 传入数组时可使用AS别名。
          let escapeStr =
            this.fieldWarpQuot(args[0]) + " AS " + this.fieldWarpQuot(args[1]);
          this.selectArgs.push(escapeStr);
        }
      });
    }
  }

  /** from表 */
  from(args: string | string[], where?: string) {
    if (typeof args === "string") {
      // const str = this.ctx.model.escape(args);
      // this.fromArgs = str.substr(1, str.length - 2);
      // this.fromArgs.push("`" + args + "`");
      this.fromArgs.push(args);
    } else if (Array.isArray(args)) {
      args.forEach((args) => {
        // this.fromArgs.push("`" + args + "`");
        this.fromArgs.push(args);
      });
    }
    if (where) {
      const cond = where.split("=");
      const str =
        this.fieldWarpQuot(cond[0]) + "=" + this.fieldWarpQuot(cond[1]);
      this.whereAndArgs.push(str);
    }
  }

  private whereAndArgs: string[] = [];
  /** and条件 */
  where_and(fieldName: string, condition: whereCondition, value: string) {
    let val = this.app.model.escape(value);
    const str = this.fieldWarpQuot(fieldName) + " " + condition + " " + val;
    this.whereAndArgs.push(str);
  }

  private whereOrArgs: string[] = [];
  /** and条件 */
  where_or(fieldName: string, condition: whereCondition, value: string) {
    let val = this.app.model.escape(value);
    const str = this.fieldWarpQuot(fieldName) + " " + condition + " " + val;
    this.whereOrArgs.push(str);
  }

  private joinArgs: string[] = [];
  join(
    tableName: string,
    condition: string,
    mode: "left" | "right" | "inner" = "left"
  ) {
    const conditions = condition
      .split("=")
      .map((item) => this.fieldWarpQuot(item))
      .join("=");
    const str = ` ${mode.toUpperCase()} JOIN ${tableName} ON ${conditions} `;
    this.joinArgs.push(str);
  }

  /** 执行查询 */
  async get() {
    if (!this.checkArgs()) return false;
    const sql = this.concatQuerySql();
    console.log(sql);
    const ret = await this.ctx.model.query(sql);
    return ret[0];
  }

  /** 拼接sql语句 */
  private concatQuerySql() {
    let sql = `${this._select}${this.selectArgs.join(",")} ${
      this._from
    } ${this.fromArgs.join(",")} ${this.joinArgs.join(" ")}`;

    // 拼接where and子句
    if (this.whereAndArgs.length > 0) {
      sql += " WHERE " + this.whereAndArgs.join(" AND ");
    }
    // 拼接where or
    if (this.whereOrArgs.length > 0) {
      if(this.whereAndArgs.length > 0){
        sql += " OR ";
      }else{
        sql += " WHERE ";
      }
      sql +=  this.whereOrArgs.join(" OR ");
    }
    
    return sql;
  }

  /** fieldDot 字段的带点的处理加上反引号 */
  private fieldWarpQuot(field: string) {
    if (field.indexOf(".") === -1) {
      return "`" + field.trim() + "`";
    } else {
      return "`" + field.trim().split(".").join("`.`") + "`";
    }
  }

  /** 检查参数是否 存在*/
  private checkArgs() {
    if (this.selectArgs.length < 1) {
      return false;
    }
    if (this.fromArgs.length < 1) return false;
    return true;
  }
}
